package cn.pconline.autoclub.task;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author chensy
 */
public class SingleClubPetrolTask {

    private static Logger log = LoggerFactory.getLogger(SingleClubPetrolTask.class);

    public static void setLog(Logger log) {
        SingleClubPetrolTask.log = log;
    }
    private String allClubIdSql = "SELECT club_id FROM ac_club where club_id = ?";
    private String dayPetrolSql = "SELECT sum(petrol) as sum_petrol FROM ac_club_petrol_log WHERE club_id = ? AND create_at >= ? AND create_at <= ? ";
    private String countClubPick = " select count(1) as num from ac_club_dyna where club_id = ? and pick >0 and type in (1,4) ";
    private String countClubRecommend = " select count(1) as num from ac_club_dyna where club_id = ? and recommend =1 and type in (1,4)";
    private String countClubCheckPetrol = " select sum(petrol) as sum_petrol from ac_member_checkin_record where club_id = ? and create_at >= ? and create_at <= ? ";
    private String updateStatDayPetrolSql = "UPDATE ac_club_stat s SET s.day_petrol = ?, s.day_petrol_update_at = ? WHERE s.club_id = ?";
    private String insertStatPetrolSqlforDay = "INSERT INTO ac_club_stat(club_id,day_petrol,day_petrol_update_at,update_at) VALUES (?,?,?,?)";
    private String insertdayPetrolSql = "INSERT INTO ac_club_petrol_daily(club_id,create_at,day_petrol,pick_num,recommend_num,check_petrol) VALUES(?,?,?,?,?,?)";
    //pick_num总精华数 recommend_num总推荐数 check_petrol当天签到油值
    private String updatedayPetrolSql = "UPDATE ac_club_petrol_daily set create_at = ? , day_petrol = ? , pick_num = ? , recommend_num = ? , check_petrol = ?  WHERE club_id = ? and create_at >= ? and create_at <= ? ";
    private String latestDaySql = "select pick_num,recommend_num,check_petrol from ac_club_petrol_daily where club_id = ? order by create_at desc limit 1";
    private String beforeDaySql = "select pick_num,recommend_num,check_petrol from ac_club_petrol_daily where club_id = ? and create_at <= ? order by create_at desc limit 1";
    private String checkPetrolSql = " select sum(check_petrol) as sum_petrol from ac_club_petrol_daily where club_id = ? and create_at >= ? and create_at <= ?";
    //-----
    private String getTotalPetrolSql = "select total_petrol FROM ac_club_petrol WHERE club_id = ?";
    private String getClubLevelSql = "select club_id,level_id from ac_club where club_id = ? ";
    private String getTotalTopicNumSql = "SELECT COUNT(1) num FROM ac_club_dyna WHERE club_id = ? and type in (1,4)";
    private String updateClubTotalSql = "UPDATE ac_club c SET c.total_petrol = ?,c.topic_num = ? WHERE c.club_id = ?";
    //同步统计表的油值等级
    private String updateStateInfoSql = "UPDATE ac_club_stat c SET c.level_id = ?,c.level_id_update_at = ?,c.total_petrol = ?, c.total_petrol_update_at = ? WHERE c.club_id = ?";
    private String insertStatPetrolSqlforTotal = "INSERT INTO ac_club_stat(club_id,total_petrol,total_petrol_update_at,update_at,level_id,level_id_update_at) VALUES (?,?,?,?,?,?)";
    //----------------排名统计相关--start
    private String getSingClubIdSql = "SELECT stat.club_id,stat.level_id,stat.total_vip FROM ac_club_stat stat left join ac_club club on stat.club_id = club.club_id  where stat.level_id > 0 and stat.club_id = ?";
    private String allIntegralRank = "SELECT  club_integral_id,NAME, petrol, remark FROM ac_club_integral WHERE club_integral_id IN (2,39,40,41,42)";
    //----------------排名统计相关--end

    public static void main(String[] args) throws SQLException, java.text.ParseException {
        //初始化Log
        AppUtils.initLog(SingleClubPetrolTask.class);

        if (args != null && args.length == 2) {
            Date d = DateUtils.parseDate(args[0]);
            long clubId = Long.parseLong(args[1]);
            SingleClubPetrolTask t = new SingleClubPetrolTask();
            AppUtils.log.info("run dailyClubPetrolTask start...");
            try {
                t.dailyClubPetrolTask(d, clubId);
            } catch (SQLException ex) {
                AppUtils.log.error("dailyClubPetrolTask fail!", ex);
            }
            AppUtils.log.info("dailyClubPetrolTask done!");

            AppUtils.log.info("run synClubTotalPetrolTask start...");
            try {
                t.synClubTotalPetrolTask(d, clubId);
            } catch (SQLException ex) {
                AppUtils.log.error("synClubTotalPetrolTask fail!", ex);
            }
            AppUtils.log.info("synClubTotalPetrolTask done!");

            AppUtils.log.info("run countClubCountryRank start...");
            try {
                t.countClubCountryRank(d, clubId);
            } catch (SQLException ex) {
                AppUtils.log.error("countClubCountryRank fail!", ex);
            }
            AppUtils.log.info("countClubCountryRank done!");
        }
    }

    /**
     * 每天检查同步总油值（顺便还要同步等级，排名需要用到） （顺便还要同步发贴总数，后台查询用到）
     *
     * @param cur
     * @throws SQLException
     */
    public void synClubTotalPetrolTask(Date cur, long clubId) throws SQLException {
        List<Club> clubIdList = new ArrayList<Club>(1024);
        Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        conn.setAutoCommit(false);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            log.info("---->统计车友会的帖子数、回帖数、总油值|同步车友会的等级、总油值到统计表---->准备执行……");
            long start = System.currentTimeMillis();
            //查询某个车友会ID和等级ID
            ps = conn.prepareStatement(getClubLevelSql);
            ps.setLong(1, clubId);
            rs = ps.executeQuery();
            while (rs.next()) {
                Club c = new Club();
                c.setClubId(rs.getLong("club_id"));
                c.setLevelId(rs.getLong("level_id"));
                clubIdList.add(c);
            }
            //逐个车友会统计昨天的油值
            for (Club club : clubIdList) {
                long cid = club.getClubId();
                long levelId = club.getLevelId();

                long totalPetrol = 0;
                //获取总油值
                ps = conn.prepareStatement(getTotalPetrolSql);
                ps.setLong(1, cid);
                rs = ps.executeQuery();
                if (rs.next()) {
                    totalPetrol = (rs.getLong("total_petrol"));
                }

                long topicNum = 0;
                //获取发贴总数
                ps = conn.prepareStatement(DBUtils.getSql(getTotalTopicNumSql, "ac_club_dyna", cid));
                ps.setLong(1, cid);
                rs = ps.executeQuery();
                if (rs.next()) {
                    topicNum = (rs.getLong("num"));
                }

                //更新车友会总油值、发帖总数、留言总数
                ps = conn.prepareStatement(updateClubTotalSql);
                ps.setLong(1, totalPetrol);
                ps.setLong(2, topicNum);
                ps.setLong(3, cid);
                ps.executeUpdate();

                //更新统计表里面的总油值、等级
                ps = conn.prepareStatement(updateStateInfoSql);
                ps.setLong(1, levelId);
                ps.setTimestamp(2, DateUtils.getTimeStamp(cur));
                ps.setLong(3, totalPetrol);
                ps.setTimestamp(4, DateUtils.getTimeStamp(cur));
                ps.setLong(5, cid);
                int uc = ps.executeUpdate();
                if (uc == 0) {//没统计记录则新增
                    ps = conn.prepareStatement(insertStatPetrolSqlforTotal);
                    ps.setLong(1, cid);
                    ps.setLong(2, 0);
                    ps.setTimestamp(3, DateUtils.getTimeStamp(cur));
                    ps.setTimestamp(4, DateUtils.getTimeStamp(cur));
                    ps.setInt(5, 0);
                    ps.setTimestamp(6, DateUtils.getTimeStamp(cur));
                    ps.executeUpdate();
                }
            }
            long end = System.currentTimeMillis();
            log.info("总共统计、同步了{}个车友会，耗时{}秒", clubIdList.size(), (end - start) / 1000.0);
            log.info("---->统计车友会的帖子数、回帖数、总油值|同步车友会的等级、总油值到统计表---->执行完毕！");
        } catch (Exception e) {
            log.error("synClubTotalPetrolTask exception!", e);
        } finally {
            conn.commit();
            conn.setAutoCommit(oldCommit);
            DBUtils.releaseConn(conn, ps, rs);
        }
    }

    /**
     * 仅给countClubRank 调用
     *
     * @param cur
     * @return
     * @throws SQLException
     */
    private void countClubCountryRank(Date cur, long clubId) throws SQLException {
        Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        conn.setAutoCommit(false);
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Rank> rankList = new ArrayList<Rank>(1024);
        int levlPetrol = 0; //等级积分基数
        int vipPetrol = 0;  //认证人数积分基数
        int pickPetrol = 0;  //精华帖积分基数
        int recommendPetrol = 0;  //推荐贴积分基数
        int checkPetrol = 0;  //签到积分基数
        try {
            log.info("---->统计全国排名---->准备执行……");
            long start = System.currentTimeMillis();
            //从参数配置表查询积分基数
            ps = conn.prepareStatement(allIntegralRank);
            rs = ps.executeQuery();
            while (rs.next()) {
                if (rs.getString("remark").equals("INTERGRL_BASE_1")) {
                    levlPetrol = rs.getInt("petrol");
                } else if (rs.getString("remark").equals("INTERGRL_BASE_2")) {
                    vipPetrol = rs.getInt("petrol");
                } else if (rs.getString("remark").equals("INTERGRL_BASE_3")) {
                    checkPetrol = rs.getInt("petrol");
                } else if (rs.getString("remark").equals("INTERGRL_BASE_4")) {
                    recommendPetrol = rs.getInt("petrol");
                } else if (rs.getString("remark").equals("PICK")) {
                    pickPetrol = rs.getInt("petrol");
                }
            }
            //查询单个车友会ID
            ps = conn.prepareStatement(getSingClubIdSql);
            ps.setLong(1, clubId);
            rs = ps.executeQuery();
            while (rs.next()) {
                Rank r = new Rank();
                r.setId(rs.getLong("club_id"));
                r.setPetrol(rs.getInt("level_id") * levlPetrol + rs.getInt("total_vip") * vipPetrol);
                rankList.add(r);
            }
            //循环处理精华、推荐、签到分数
            for (Rank rank : rankList) {
                int nowPick = 0;
                int nowRecommend = 0;
                int oldPick = 0;
                int oldRecommend = 0;
                clubId = rank.getId();
                int nowPetrol = rank.getPetrol();
                int check = 0;
                log.info("车友会{}旧的活跃度：{}", clubId, nowPetrol);
                //从车友会每天的油值记录表中获取最近的精华总数和推荐总数
                ps = conn.prepareStatement(latestDaySql);
                ps.setLong(1, clubId);
                rs = ps.executeQuery();
                if (rs.next()) {
                    nowPick = rs.getInt("pick_num");
                    nowRecommend = rs.getInt("recommend_num");
                }

                //从车友会每天的油值记录表中获取30天前的精华总数和推荐总数
                ps = conn.prepareStatement(beforeDaySql);
                ps.setLong(1, clubId);
                ps.setTimestamp(2, DateUtils.getSomeDateEnd(cur, -30));
                rs = ps.executeQuery();
                if (rs.next()) {
                    oldPick = rs.getInt("pick_num");
                    oldRecommend = rs.getInt("recommend_num");
                }

                //从车友会每天的油值记录表中获取签到的总油值
                ps = conn.prepareStatement(checkPetrolSql);
                ps.setLong(1, clubId);
                ps.setTimestamp(2, DateUtils.getSomeDateStart(cur, -30));
                ps.setTimestamp(3, DateUtils.getLastDayEnd(cur));
                rs = ps.executeQuery();
                if (rs.next()) {
                    check = rs.getInt("sum_petrol");
                }

                //计算活跃度

                //加上精华分和推荐分
                nowPetrol += (nowPick - oldPick) * pickPetrol;
                nowPetrol += (nowRecommend - oldRecommend) * recommendPetrol;
                nowPetrol += check * checkPetrol;
                //如果计算的活跃度<0时，活跃度=0
                nowPetrol = nowPetrol <= 0 ? 0 : nowPetrol;
                rank.setPetrol(nowPetrol);
                log.info("车友会{}现在的活跃度：{}", clubId, nowPetrol);
                log.info("旧的精华数：{}，推荐数：{}", oldPick, oldRecommend);
                log.info("现在的精华数：{}，推荐数：{}", nowPick, nowRecommend);
            }

            long end = System.currentTimeMillis();
            log.info("先做一次查询，查出积分基数，再做一次查询，查出所有车友会，然后每个三次查询，两次更新，共统计了{}个车友会，耗时{}秒",
                    rankList.size(), (end - start) / 1000.0);
            log.info("---->统计全国排名---->执行完毕！");
        } catch (Exception e) {
            log.error("countClubCountryRank exception!", e);
        } finally {
            conn.commit();
            conn.setAutoCommit(oldCommit);
            DBUtils.releaseConn(conn, ps, rs);
        }
    }

    /**
     * 统计车友会每天（昨天）增加的油值
     *
     * @param cur 当前日期
     * @throws SQLException
     */
    public void dailyClubPetrolTask(Date cur, long clubId) throws SQLException {
        List<Long> clubIdList = new ArrayList<Long>(512);
        Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        conn.setAutoCommit(false);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            log.info("---->每天统计车友会油值---->准备执行……");
            long start = System.currentTimeMillis();

            //查询所有车友会ID
            ps = conn.prepareStatement(allClubIdSql);
            ps.setLong(1, clubId);
            rs = ps.executeQuery();
            while (rs.next()) {
                clubIdList.add(rs.getLong("club_id"));
            }

            //逐个车友会统计昨天的油值
            for (Long id : clubIdList) {
                long dayPetrol = 0;
                //从日志中获取车友会昨天增加的油值（日志表有分表）
                ps = conn.prepareStatement(DBUtils.getSql(dayPetrolSql, "ac_club_petrol_log", DateUtils.getLastDay(cur)));
                ps.setLong(1, id);
                ps.setTimestamp(2, DateUtils.getLastDayStart(cur));
                ps.setTimestamp(3, DateUtils.getLastDayEnd(cur));
                rs = ps.executeQuery();
                if (rs.next()) {
                    dayPetrol = (rs.getLong("sum_petrol"));
                }

                //更新车友会昨天油值（排行统计表）
                ps = conn.prepareStatement(updateStatDayPetrolSql);
                ps.setLong(1, dayPetrol);
                ps.setTimestamp(2, DateUtils.getTimeStamp(cur));
                ps.setLong(3, id);
                int uc = ps.executeUpdate();
                if (uc == 0) {//没统计记录，则创建
                    ps = conn.prepareStatement(insertStatPetrolSqlforDay);
                    ps.setLong(1, id);
                    ps.setLong(2, dayPetrol);
                    ps.setTimestamp(3, DateUtils.getTimeStamp(cur));
                    ps.setTimestamp(4, DateUtils.getTimeStamp(cur));
                    ps.executeUpdate();
                }

                if (dayPetrol == 0) {
                    continue;//如果当天油值没变化就无需增加日记录
                }

                /**
                 * 统计当前精华帖
                 */
                int pickNum = 0;
                ps = conn.prepareStatement(DBUtils.getSql(countClubPick, "ac_club_dyna", id));
                ps.setLong(1, id);
                rs = ps.executeQuery();
                while (rs.next()) {
                    pickNum = (rs.getInt("num"));
                }

                /**
                 * 统计当前推荐贴
                 */
                int recommendNum = 0;
                ps = conn.prepareStatement(DBUtils.getSql(countClubRecommend, "ac_club_dyna", id));
                ps.setLong(1, id);
                rs = ps.executeQuery();
                while (rs.next()) {
                    recommendNum = (rs.getInt("num"));
                }

                /**
                 * 统计昨天签到的油值
                 */
                int checkTotal = 0;
                ps = conn.prepareStatement(DBUtils.getSql(countClubCheckPetrol, "ac_member_checkin_record", DateUtils.getLastDay(cur)));
                ps.setLong(1, id);
                ps.setTimestamp(2, DateUtils.getLastDayStart(cur));
                ps.setTimestamp(3, DateUtils.getLastDayEnd(cur));
                rs = ps.executeQuery();
                if (rs.next()) {
                    checkTotal = (rs.getInt("sum_petrol"));
                }

                //记录车友会昨天统计结果 （每天最多只有一条记录，先更新再插入，可以避免重复执行）
                ps = conn.prepareStatement(updatedayPetrolSql);
                ps.setTimestamp(1, new java.sql.Timestamp(System.currentTimeMillis()));
                ps.setLong(2, dayPetrol);
                ps.setInt(3, pickNum);
                ps.setInt(4, recommendNum);
                ps.setInt(5, checkTotal);
                ps.setLong(6, id);
                ps.setTimestamp(7, DateUtils.getLastDayStart(cur));
                ps.setTimestamp(8, DateUtils.getLastDayEnd(cur));
                uc = ps.executeUpdate();
                if (uc == 0) {
                    ps = conn.prepareStatement(insertdayPetrolSql);
                    ps.setLong(1, id);
                    //统计时间是昨天!!!!
                    ps.setTimestamp(2, DateUtils.getLastDay(cur));
                    ps.setLong(3, dayPetrol);
                    ps.setInt(4, pickNum);
                    ps.setInt(5, recommendNum);
                    ps.setInt(6, checkTotal);
                    ps.executeUpdate();
                }
            }
            long end = System.currentTimeMillis();
            log.info("先做一次查询，查出所有车友会，然后每个车友会做4次查询和2次更新，共统计了{}个车友会，耗时{}秒",
                    clubIdList.size(), (end - start) / 1000.0);
            log.info("---->每天统计车友会油值---->执行完毕！");
        } catch (Exception e) {
            log.error("dailyClubPetrolTask excpetion!", e);
        } finally {
            conn.commit();
            conn.setAutoCommit(oldCommit);
            DBUtils.releaseConn(conn, ps, rs);
        }
    }
}
